# coding:utf-8
import traceback

import pymysql
from pymysql import Connection

connection: Connection = None


def get_connection():
    global connection
    if connection is None:
        try:
            connection = pymysql.connect(host="mysql.sqlpub.com", user="root_remote", password="123456", port=3306,
                                         db="xs_db_all", charset="utf8")
        except IOError:
            traceback.print_exc(file=open("log.txt", "a"))
            connection.close()
    return connection


def execute_query(sql) -> list:
    get_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        data = cursor.fetchall()
        return data


def execute_update(sql) -> None:
    get_connection()
    with connection.cursor() as cursor:
        cursor.execute(sql)
        connection.commit()


def get_classes(fetchNameProp=True) -> list:
    classList = []
    sql = "select id, name, create_time, update_time from db_class"
    data = execute_query(sql)
    for row in data:
        if fetchNameProp:
            classList.append(row[1])
        else:
            classList.append(row)
    return classList


def add_classes(colVals):
    sql = "insert into db_class(name, create_time, update_time) values('{}', '{}', '{}')".format(*colVals)
    execute_update(sql)


def delete_classes(prop):
    sql = "delete from db_class where id = {}".format(prop)
    execute_update(sql)


def get_students(fetchNameProp=True, class_id=None) -> list:
    studentList = []
    sql = "select number, name, age from db_student where class_id={}".format(class_id)
    data = execute_query(sql)
    for row in data:
        if fetchNameProp:
            studentList.append(row[1])
        else:
            studentList.append(row)
    return studentList


def add_students(colVals):
    sql = "insert into db_student(class_id, number, name, age) values({}, '{}', '{}', {})"
    if isinstance(colVals, list):
        for row in colVals:
            sql = sql.format(*row)
            execute_update(sql)
    else:
        sql = sql.format(*colVals)
        execute_update(sql)


def delete_students(prop):
    sql = "delete from db_student where class_id = {}".format(prop)
    execute_update(sql)
